/*
  # Add full-text search to existing table_data

  1. Updates
    - Add search vector column
    - Create trigger function for automatic updates
    - Add GIN index for search performance
  
  2. Security
    - Ensure RLS is enabled
    - Add public read access policy
*/

-- Add search vector column if it doesn't exist
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'table_data' AND column_name = 'search_vector'
  ) THEN
    ALTER TABLE table_data ADD COLUMN search_vector tsvector;
  END IF;
END $$;

-- Create or replace function to update search vector
CREATE OR REPLACE FUNCTION update_table_data_search_vector()
RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('chinese', COALESCE(NEW.title, '')), 'A') ||
    setweight(to_tsvector('chinese', COALESCE(NEW.content, '')), 'B') ||
    setweight(to_tsvector('chinese', COALESCE(array_to_string(NEW.search_terms, ' '), '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Drop and recreate trigger
DROP TRIGGER IF EXISTS table_data_search_vector_update ON table_data;
CREATE TRIGGER table_data_search_vector_update
  BEFORE INSERT OR UPDATE ON table_data
  FOR EACH ROW
  EXECUTE FUNCTION update_table_data_search_vector();

-- Create index if it doesn't exist
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_indexes 
    WHERE tablename = 'table_data' AND indexname = 'table_data_search_idx'
  ) THEN
    CREATE INDEX table_data_search_idx ON table_data USING gin(search_vector);
  END IF;
END $$;

-- Update existing rows
UPDATE table_data 
SET search_vector = 
  setweight(to_tsvector('chinese', COALESCE(title, '')), 'A') ||
  setweight(to_tsvector('chinese', COALESCE(content, '')), 'B') ||
  setweight(to_tsvector('chinese', COALESCE(array_to_string(search_terms, ' '), '')), 'C')
WHERE search_vector IS NULL;